/**
* @author Max Rupplin
* @date 4-23-08
* @file ddas_science_db_purger.C
*/

#include <stdlib.h>
#include <iostream>
#include <boinc_db.h>

#define DBHOST 		"localhost"
#define PASSWD 		"border8max"
#define USERNAME 	"boinc"
#define DBNAME 		"ddas"

using namespace std;

int keep_only_top_n_results( int n, int jobID );
int get_num_results_for_job( int jobID );
void get_job_ids( int* jobIDs, my_ulonglong* num_rows );

/**
* Keeps the science database trim - if a researcher wants the top N results from a job, the database need only store N results. This program tells the database to drop results over the number of expected results, keeping the best N results based on SSE for a given job.
*/
int main()
{
	#ifdef DEBUG_MODE
	cout<<"ddas_science_db_purger starting..."<<endl;
	#endif

	/// An array of job IDs
	int* jobIDs = new int[100];

	/// The number of rows (jobs) in the MYSQL_RES (a query result)
	my_ulonglong num_rows;

	//get job IDs from ddas database
	get_job_ids( jobIDs, &num_rows );

	//for each job
	for( my_ulonglong i=0; i<num_rows; i++)
	{
		//find out how many results the researcher expects back for a given job (top N results)
		int num_results = get_num_results_for_job( jobIDs[((unsigned long)i)] );

		//error handling
		if(num_results == 0) continue;

		//check for duplicate results, drop any if found
		//unimplemented - dont think it will ever happen

		//for all results for a given job, sort asc by SSE drop from database if SSE is higher than nth SSE
		keep_only_top_n_results( num_results, jobIDs[i] );
 	}

	//free memory
	delete[] jobIDs;

	#ifdef DEBUG_MODE
	cout<<"ddas_science_db_purger is returning..."<<endl;
	#endif
}


/**
* Return the job IDs from the database and store how many were found in num_rows
* 
* @param jobIDs The job IDs
* @param num_rows The number of job IDs found in the database
*/
void get_job_ids( int* jobIDs, my_ulonglong* num_rows)
{
        /// The C MYSQL pointer
        MYSQL* mysql = mysql_init(0);

        if (!mysql)
        {
                #ifdef DEBUG_MODE
                cout<<"MYSQL initialization failed..."<<endl;
                #endif

                return;
        }

        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DBNAME, 0, 0, 0);

        if (mysql == 0)
        {
                #ifdef DEBUG_MODE
                cout<<"MYSQL connection failed..."<<endl;
                #endif

                return;
        }
        else
        {
                #ifdef DEBUG_MODE
                cout<<"MYSQL connection established..."<<endl;
                #endif
        }

	//space for MYSQL query
        char* buffer = new char[256];

	//MYSQL query
        sprintf(buffer, "select problemID from ddas_problem" );

	//perform query + error handling
        if(mysql_query(mysql, buffer))
        {
                #ifdef DEBUG_MODE
                cout<<"MYSQL query failed..."<<endl;
                #endif

		delete[] buffer;

                return;
        }
        else
        {
                MYSQL_RES* result = mysql_store_result(mysql);

                *num_rows = mysql_num_rows(result);

                //jobIDs = new int[*num_rows];

                for( my_ulonglong i=0; i<*num_rows; i++ )
                {
                        MYSQL_ROW row = mysql_fetch_row(result);

                        #ifdef DEBUG_MODE
                        cout<<"ddas_science_db_purger found "<< ((unsigned long)*num_rows) <<" jobs in the ddas database."<<endl;
                        #endif

                        jobIDs[i] = atoi(row[0]);
			cout<<atoi(row[0])<<endl;
			cout<<jobIDs[i]<<endl;
                }

                mysql_free_result(result);

                #ifdef DEBUG_MODE
                //cout<<"ddas_science_db_purger returning 1 (success)."<<endl;
                #endif
        }

        //free memory 
        delete[] buffer;

        //close connection
        mysql_close(mysql);
}

/**
* Keeps only a certain number of results in the database based on SSE and deleting the rest
* 
* @param results_to_keep The number of results to keep for this job
* @param jobID The job in question
* @return Returns 1 if successful, 0 otherwise
*/
int keep_only_top_n_results ( int results_to_keep, int jobID )
{
        /// The C MYSQL pointer
        MYSQL* mysql = mysql_init(0);

        if (!mysql)
        {
                #ifdef DEBUG_MODE
                cout<<"MYSQL initialization failed..."<<endl;
                #endif

                return 0;
        }

        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, "science", 0, 0, 0);

        if (mysql == 0)
        {
                #ifdef DEBUG_MODE
                cout<<"MYSQL connection failed..."<<endl;
                #endif

                return 0;
        }
        else
        {
                #ifdef DEBUG_MODE
                cout<<"MYSQL connection established..."<<endl;
                #endif
        }

        char* buffer1 	= new char[1024];
	char* buffer2	= new char[1024];
	char* buffer3  	= new char[1024];

	//returns the nth best SSE for a given job (if results_to_keep = 10, this should return the 10th best SSE)
        sprintf(buffer1, "select sse from (select sse from science.canonical_results join ddas.workunit_to_job_mapping where ddas.workunit_to_job_mapping.job_id = %u AND ddas.workunit_to_job_mapping.wu_id = science.canonical_results.wu_id order by sse asc limit %u) as derived_table order by sse desc limit 1", jobID, results_to_keep );

	//returns the SSEs for a given set of workunits defined by a jobID
	sprintf(buffer2, "select sse from science.canonical_results join ddas.workunit_to_job_mapping on ddas.workunit_to_job_mapping.wu_id = science.canonical_results.wu_id where ddas.workunit_to_job_mapping.job_id = %u", jobID );

	//perform query + error handling
        if( mysql_query(mysql, buffer2) )
        {
                #ifdef DEBUG_MODE
                cout<<"MYSQL query failed..."<<endl;
                #endif

        	//free char[]
        	delete[] buffer1;
        	delete[] buffer2;
        	delete[] buffer3;

        	//close connection
	        mysql_close(mysql);

                return 0;
        }
	//successful query
        else
        {
		//store result
                MYSQL_RES* result = mysql_store_result(mysql);

		//how many rows were returned
                int num_rows = mysql_num_rows(result);

		//free memory
		mysql_free_result(result);

		//if we aren't over our quota simply return
		if( num_rows <= results_to_keep )
		{
        		//free char[]
        		delete[] buffer1;
        		delete[] buffer2;
       	 		delete[] buffer3;

        		//close connection
		        mysql_close(mysql);

			return 1;
		}
		else
		{
			//get nth best result
			if( mysql_query(mysql, buffer2) )
			{
                                //free char[]
                                delete[] buffer1;
                                delete[] buffer2;
                                delete[] buffer3;

                                //close connection
                                mysql_close(mysql);

                                return 0;
			}

			//store result
			MYSQL_RES* result = mysql_store_result(mysql);			

			//grab first (only) row
			MYSQL_ROW row = mysql_fetch_row(result);

			// The value of the nth best SSE for a given job
			float nth_best = atof(row[0]);

			//free memory
			mysql_free_result(result);

			// MYSQL delete stmt
			sprintf(buffer3, "delete from science.canonical_result where job_id = %u AND sse > %f", jobID, nth_best);

			//perform query + error handling
			if( mysql_query( mysql, buffer3 ) )
			{
        			//free char[]
        			delete[] buffer1;
       	 			delete[] buffer2;
        			delete[] buffer3;

        			//close connection
		        	mysql_close(mysql);

				return 0;
			}
		}
        }
	
        //free char[]
        delete[] buffer1;
	delete[] buffer2;
	delete[] buffer3;

        //close connection
        mysql_close(mysql);

	return 1;
}


/*
* Gets the number of results the researcher expects to see at the end of the job
*
* @param jobID The job in question
* @return Returns 1 if successful, 0 otherwise
*/
int get_num_results_for_job( int jobID )
{
	cout<<"her!"<<endl;

        MYSQL* mysql = mysql_init(0);

        if (!mysql)
        {
                #ifdef DEBUG_MODE
                cout<<"MYSQL initialization failed..."<<endl;
                #endif

                return 0;
        }

        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DBNAME, 0, 0, 0);

	//error handling
        if (mysql == 0)
        {
                #ifdef DEBUG_MODE
                cout<<"MYSQL connection failed..."<<endl;
                #endif

                return 0;
        }
        else
        {
                #ifdef DEBUG_MODE
                cout<<"MYSQL connection established..."<<endl;
                #endif
        }

	//space for MYSQL query
	char* buffer = new char[256];

	//MYSQL query
        sprintf(buffer, "select numResults from ddas_problem where problemID = %u", jobID);

	//perform query + error handling
	if( mysql_query(mysql, buffer) )
        {
                #ifdef DEBUG_MODE
                cout<<"ddas_science_db_purger: get_num_results_for_job: MYSQL query failure..."<<endl;
                #endif

		//close connection
		mysql_close(mysql);

		//free memory
		delete[] buffer;

                return 0;
        }
        else
        {
		//store the result
                MYSQL_RES* result = mysql_store_result(mysql);
        	
		//if the result contained 1+ rows
		if( mysql_num_rows(result) > 0 )
		{        
			//get first (only) row
			MYSQL_ROW row = mysql_fetch_row(result);

			//how many results?
                	int size = atoi(row[0]);
                
			//free memory
                	mysql_free_result(result);

			//close connection
			mysql_close(mysql);		
	
			//free memory
			delete[] buffer;

			return size;
		}

		//free memory		
		mysql_free_result(result);
		
		//close connection
		mysql_close(mysql);

		//free memory
		delete[] buffer;

		return 0;
        }
}
